﻿CREATE PROCEDURE admin.s_Object_SchemaBind
	@ObjectName sysname,
	@Remove bit = 0
AS
DECLARE @SQL nvarchar(max),@Type char(2),@SB sysname SET @SB = 'WITH SCHEMABINDING'
SELECT @SQL = ISNULL(@SQL,'')+Text,@Type = Type FROM sys.objects o INNER JOIN syscomments c ON o.object_id = c.id
WHERE o.Name = @ObjectName AND OBJECTPROPERTY(o.object_id,'IsSchemaBound') = @Remove

IF @Remove = 0 BEGIN
	IF @Type = 'V '
		SET @SQL = dbo.clr_RegEx_Replace(dbo.clr_RegEx_Replace(@SQL,'(\s*)CREATE(\s+)(VIEW)','$1ALTER$2$3',1,0,1),'(\s)AS(\s)',' '+@SB+'$1AS$2',1,0,1)
	ELSE
		SET @SQL = dbo.clr_RegEx_Replace(dbo.clr_RegEx_Replace(@SQL,'(\s*)CREATE(\s+)(FUNCTION)','$1ALTER$2$3',1,0,1),
			'(\)\s*RETURNS.+?)(\s)(?:AS\s+)?(BEGIN\s|RETURN\s)','$1$2'+@SB+'$2$3',1,0,1)
END ELSE
	SET @SQL = dbo.clr_RegEx_Replace(dbo.clr_RegEx_Replace(@SQL,'\s+WITH\s+SCHEMABINDING','',1,0,1),'^\s*CREATE\s','ALTER ',1,0,1)
SET @SQL = dbo.clr_RegEx_Replace(@SQL,'(\s)(v_TradeReferences)','$1dbo.$2',10,0,1)
IF @SQL > '' PRINT @SQL
PRINT(@SQL)

